package db.queries;

import java.sql.SQLException;
import java.util.Collection;
import java.util.List;

import core.exceptions.DBAccessException;
import core.objects.Book;
import core.objects.City;
import core.objects.Country;
import core.objects.ObjectWithID;
import core.objects.User;
import db.DBHandler;
import db.ICrossInsert;
import db.dbTables.AbstractDBTable;
import db.dbTables.BookTable;
import db.dbTables.CityTable;
import db.dbTables.CountryTable;
import db.dbTables.DbColumn;
import db.dbTables.UserTable;
import db.queries.core.BaseObjectQueriesInfo;
import db.queries.core.QueriesEnvironment;

public class UserQueries extends BaseObjectQueriesInfo<User> {
	
	private final static int QUERY_TYPE_REGULAR = 0;
	private final static int QUERY_TYPE_CROSS_BOOK = 1;
	private final static int QUERY_TYPE_CITY = 2;
	private final static int QUERY_TYPE_COUNTRY = 3;
		
	private int queryType = QUERY_TYPE_REGULAR;
	private boolean selectObj = false;
	
	@Override
	protected AbstractDBTable getTable() {
		if (queryType == QUERY_TYPE_CROSS_BOOK) {
			return QueriesEnvironment.getUserCrossBookTable();
		}
		return QueriesEnvironment.getUserTable();
	}
	
	@Override
	public List<User> selectContains(String name, ObjectWithID obj)
			throws DBAccessException {
		return selectContains(name, obj, false);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<User> selectContains(String name, ObjectWithID obj,
			boolean limitSearch) throws DBAccessException {
		
		if (obj instanceof User) {
		
			try {
				return (List<User>)DBHandler.convertResultSetToBaseObjectList(
						DBHandler.executeQuery(
								QueriesEnvironment.selectQuery(
										QueriesEnvironment.getUserTable().first_name.getName() + " LIKE ? "
										+ " OR " + QueriesEnvironment.getUserTable().last_name.getName() + " LIKE ? ",
										new Object[] { "%" + name + "%", "%" + name + "%" },
										QueriesEnvironment.getUserTable().getDbColumns().toArray(new DbColumn[0]),
										limitSearch)),
								QueriesEnvironment.getUserTable());
			} catch (SQLException e) {
				throw handleSQLException(e);
			}
			
		} else {
			return super.selectContains(name, obj, limitSearch);
		}
	}
	
	@Override
	protected DBAccessException handleSQLException(SQLException e) {
		if (e.getMessage() != null) {
			String message = e.getMessage();
			if (message.startsWith("Duplicate entry") && message.contains("username_UNIQUE")) {
				return new DBAccessException("username already exists", e);
			}
		}
		return super.handleSQLException(e);
	}
	
	@Override
	protected DBAccessException foreignKeyConstrainstViolation(String key,
			Throwable e) {

		if (key.equals(UserTable.COLUMN_ID)) {
			return new DBAccessException("Error: unknown user", e);
		} else if (key.equals(BookTable.COLUMN_ID)) {
			return new DBAccessException("Error: No such Book to add to user", e);
		} else if (key.equals(CityTable.COLUMN_ID)) {
			return new DBAccessException("Error: City does not exist", e);
		} else if (key.equals(CountryTable.COLUMN_ID)) {
			return new DBAccessException("Error: Country does not exist", e);
		} else {
			return new DBAccessException("Unknown error has occured", e);
		}
		
	}


	@Override
	protected DbColumn getNameColumn() {
		if (queryType == QUERY_TYPE_CROSS_BOOK) {
			return QueriesEnvironment.getBookTable().name;
		}
		return QueriesEnvironment.getUserTable().username;
	}

	@Override
	protected DbColumn getIDColumn() {
		switch (queryType) {
			case QUERY_TYPE_CROSS_BOOK:
				if (selectObj) {
					return QueriesEnvironment.getUserCrossBookTable().bookId;
				}
				return QueriesEnvironment.getUserCrossBookTable().userId;
			case QUERY_TYPE_CITY:
				if (selectObj) {
					return QueriesEnvironment.getUserTable().city_id;
				}
				break;
			case QUERY_TYPE_COUNTRY:
				if (selectObj) {
					return QueriesEnvironment.getUserTable().country_id;
				}
				break;
		}
		return QueriesEnvironment.getUserTable().id;
	}

	@Override
	protected void determineTable(ObjectWithID obj, boolean selectObj) {
		this.selectObj = selectObj;
		
		if (obj instanceof Book) {
			queryType = QUERY_TYPE_CROSS_BOOK;
		} else if (obj instanceof City) {
			queryType = QUERY_TYPE_CITY;
		} else if (obj instanceof Country) {
			queryType = QUERY_TYPE_COUNTRY;
		} else {
			queryType = QUERY_TYPE_REGULAR;
		}
	}

	@Override
	protected DbColumn getAbsoluteIDColumn() {
		return QueriesEnvironment.getUserTable().id;
	}

	@Override
	protected ICrossInsert getCross() {
		return new ICrossInsert() {
			
			@Override
			public Collection<? extends ObjectWithID> getCrossObjects(ObjectWithID obj) {
				if (queryType == QUERY_TYPE_CROSS_BOOK)
					return ((User)obj).getBooks();
				return null;
			}
		};
	}
}
